﻿using System;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using System.Globalization;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Trace;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;

namespace SQLServerHealthCheck.SQLServer
{
    public static class SQLTrace
    {      
        
        public static List<Exception> errors;


        private static Regex _binaryConstant = new Regex("0X([0-9ABCDEF])+", RegexOptions.Compiled);
        private static Regex _blockComment = new Regex(@"/\*.*?\*/", RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _blockCommentDelimiters = new Regex(@"/\*|\*/", RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _brackets = new Regex(@"(\[|\])", RegexOptions.Compiled);
        private static Regex _cursor = new Regex(@"EXEC\s+SP_CURSOROPEN\s+(@CURSOR\s*=\s*)?\@P1\s+OUTPUT\,\s*(@STMT\s*=\s*)?(N)?(?<tick>')  (?<statement>      ((  (?!\k<tick>)  .|\k<tick>{2})*)   )    \k<tick>", RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _cursorPrepExec = new Regex("EXEC\r\n\\s+     # any spaces\r\nsp_cursorprepexec\r\n.+       # any characters up to the string\r\nN  \r\n(?<tick>')   # matches an apostraphe\r\n(?!@)    # but no @ following\r\n(?<statement>   ((  (?!\\k<tick>)  .|\\k<tick>{2})*)   )    # all the characters ...\r\n\\k<tick>   # until the next tick that isn't doubled.", RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _dbAndObjectName = new Regex(@"EXEC(UTE){0,1}\s+(?<database>\w+)\.\.(?<object>\w+)", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _delimiterStart = new Regex(@"(--)|(/\*)|'", RegexOptions.Compiled);
        private static Regex _doubleApostrophe = new Regex("('')(?<string>.*?)('')", RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _emptyString = new Regex(@"\'\'", RegexOptions.Compiled);
        private static Regex _execPrepped = new Regex(@"^EXEC\s+SP_EXECUTE\s+(?<stmtnum>\d+)", RegexOptions.Compiled);
        private static Regex _execUnprep = new Regex(@"EXEC\s+SP_UNPREPARE\s+(?<stmtnum>\d+)", RegexOptions.Compiled);
        private static Regex _inClause = new Regex(@"IN\s*\(\s*\{.*\}\s*\)", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _inlineComment = new Regex("--.*$", RegexOptions.Compiled | RegexOptions.Multiline);
        private static Regex _numericConstant = new Regex(@"(?<prefix>[\(\s,=\-><\!\&\|\+\*\/\%\~\$])(?<digits>[\-\.\d]+)", RegexOptions.Compiled);
        private static Regex _objectName = new Regex(@"EXEC(UTE){0,1}\s(?<schema>(\w+\.)*)(?<object>\w+)", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _prepareSql = new Regex(@"SET\s+@P1=(?<stmtnum>(NULL|\d+))\s+EXEC\s+(?<preptype>SP_PREP(ARE|EXEC))\s+@P1\s+OUTPUT,\s*(NULL|(N\'.+?\')),\s*N(?<remaining>.+)$", RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _prepExecRpc = new Regex(@"SET\s+@P1=(?<stmtnum>\d+)\s+EXEC\s+SP_PREPEXECRPC\s+@P1\s+OUTPUT,\s*N\'(?<statement>.+?)'", RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _preppedSqlStatement = new Regex(@"^(')(?<statement>((?!\1).|\1{2})*)\1", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _spaces = new Regex(@"\s+", RegexOptions.Compiled);
        private static Regex _spExecuteSql = new Regex(@"EXEC\s+SP_EXECUTESQL\s+N\'(?<statement>.+?)\'", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _spreadCsv = new Regex(@",(?=\S)", RegexOptions.Compiled);
        private static Regex _stringConstant = new Regex(@"(')(((?!\1).|\1{2})*)\1", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _unicodeConstant = new Regex("N{STR}", RegexOptions.Compiled);
        private const int CURRENT_DATABASE_VERSION = 2;
        private static Hashtable prepSql = new Hashtable();




        public static List<Exception> CreateJobToCaptureTrace(string SQLServerInstance, string Database, DateTime Startdate, DateTime StartTime,DateTime Enddate, DateTime EndTime)
        {
            try
            {

                errors = new List<Exception>();

                CreateJob(SQLServerInstance, Database, Startdate, StartTime, Enddate, EndTime);

                            
            }
            catch (System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to delete  file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return errors;

        }



        private static void CreateJob(string SQLServerInstance, string Database, DateTime Startdate, DateTime StartTime, DateTime Enddate, DateTime EndTime)
        {

            try
            {
                //Key point to note here is if yu want to double quotes  string in TSQL and ADO.NET
                //then you may need to replace double quotes with 2 double quotes or use a escape sequence \

                //Check the connection to SQL Server Instance and gets the details.
                if (InstallationCheck.IsSQLServerAccessible(@"Server=" + SQLServerInstance + ";database=master;Integrated Security=True"))
                {

                    using (SqlConnection cn = new SqlConnection(@"Server=" + SQLServerInstance + ";Database=master;Integrated Security=True"))
                    {
                        cn.Open();

                        string sqlOLE1 = "sp_configure 'Agent XPs',1 ;reconfigure with override;";
                        SqlCommand OLEcmd1 = new SqlCommand(sqlOLE1, cn);
                        OLEcmd1.ExecuteNonQuery();



                        string strStartdate = AddPrefixtoString(Startdate.Year.ToString()) + AddPrefixtoString(Startdate.Month.ToString()) + AddPrefixtoString(Startdate.Day.ToString());//stores the start date
                        string strStartTime = AddPrefixtoString(StartTime.Hour.ToString()) + AddPrefixtoString(StartTime.Minute.ToString()) + AddPrefixtoString(StartTime.Second.ToString());//s tores the start time

                        string strEndDate = AddPrefixtoString(Enddate.Year.ToString()) + "-" + AddPrefixtoString(Enddate.Month.ToString()) + "-" + AddPrefixtoString(Enddate.Day.ToString());//stores the end date
                        string strEndTime = AddPrefixtoString(EndTime.Hour.ToString()) + AddPrefixtoString(EndTime.Minute.ToString()) + AddPrefixtoString(EndTime.Second.ToString()); //stores the end time

                        string strStopTime = strEndDate + " " + AddPrefixtoString(EndTime.Hour.ToString()) + ":" + AddPrefixtoString(EndTime.Minute.ToString()) + ":" + AddPrefixtoString(EndTime.Second.ToString());

                        //inserts the details in Trace_Server table in SQLServerHealthCheckDeploy datbase and returns a unique Job Name
                        string jobName = GetJobName(SQLServerInstance, Database, Startdate.Date.ToShortDateString(), Enddate.Date.ToShortDateString(), StartTime.ToShortTimeString(), EndTime.ToShortTimeString());






                        //Drops  Proc_CaptureTrace for every job in master database
                        String sqldropproc = @"	
                                                USE [master]

                                                IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_CaptureTrace_ " + jobName.Trim() + @"]') AND type in (N'P', N'PC'))
                                                DROP PROCEDURE [dbo].[Proc_CaptureTrace_" + jobName.Trim() + @"]
                                                ";

                        SqlCommand cmd = new SqlCommand(sqldropproc, cn);
                        cmd.ExecuteNonQuery();

                        //Creates   Proc_CaptureTrace uniquely for every job in master database

                        String sqlcreateproc = @"

                                            create PROCEDURE [dbo].[Proc_CaptureTrace_"+jobName.Trim() +"]" +@" @StopTime datetime -- Time when Trace need to be stopped
                                                ,@FileName nvarchar(256)--Trace File Name
                                            , @TraceID int OUTPUT -- Return the TraceID to the caller.
                                            WITH RECOMPILE
                                            AS
                                            -- Create a Queue
                                            declare @rc int
                                            declare @dbaTraceID int
                                            declare @maxfilesize bigint
                                            set @maxfilesize = 5 

                                            -- Please replace the text InsertFileNameHere, with an appropriate
                                            -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
                                            -- will be appended to the filename automatically. If you are writing from
                                            -- remote server to local drive, please use UNC path and make sure server has
                                            -- write access to your network share



                                            exec @rc = sp_trace_create @TraceID output, 0, @FileName, @maxfilesize, @StopTime

                                            if (@rc != 0) goto error 

                                            -- Client side File and Table cannot be scripted

                                            -- Set the events
                                            declare @on bit
                                            set @on = 1
                                            exec sp_trace_setevent @TraceID, 10, 7, @on
                                            exec sp_trace_setevent @TraceID, 10, 15, @on
                                            exec sp_trace_setevent @TraceID, 10, 31, @on
                                            exec sp_trace_setevent @TraceID, 10, 8, @on
                                            exec sp_trace_setevent @TraceID, 10, 16, @on
                                            exec sp_trace_setevent @TraceID, 10, 48, @on
                                            exec sp_trace_setevent @TraceID, 10, 64, @on
                                            exec sp_trace_setevent @TraceID, 10, 1, @on
                                            exec sp_trace_setevent @TraceID, 10, 9, @on
                                            exec sp_trace_setevent @TraceID, 10, 17, @on
                                            exec sp_trace_setevent @TraceID, 10, 25, @on
                                            exec sp_trace_setevent @TraceID, 10, 41, @on
                                            exec sp_trace_setevent @TraceID, 10, 49, @on
                                            exec sp_trace_setevent @TraceID, 10, 2, @on
                                            exec sp_trace_setevent @TraceID, 10, 10, @on
                                            exec sp_trace_setevent @TraceID, 10, 18, @on
                                            exec sp_trace_setevent @TraceID, 10, 26, @on
                                            exec sp_trace_setevent @TraceID, 10, 34, @on
                                            exec sp_trace_setevent @TraceID, 10, 50, @on
                                            exec sp_trace_setevent @TraceID, 10, 66, @on
                                            exec sp_trace_setevent @TraceID, 10, 3, @on
                                            exec sp_trace_setevent @TraceID, 10, 11, @on
                                            exec sp_trace_setevent @TraceID, 10, 35, @on
                                            exec sp_trace_setevent @TraceID, 10, 51, @on
                                            exec sp_trace_setevent @TraceID, 10, 4, @on
                                            exec sp_trace_setevent @TraceID, 10, 12, @on
                                            exec sp_trace_setevent @TraceID, 10, 60, @on
                                            exec sp_trace_setevent @TraceID, 10, 13, @on
                                            exec sp_trace_setevent @TraceID, 10, 6, @on
                                            exec sp_trace_setevent @TraceID, 10, 14, @on
                                            exec sp_trace_setevent @TraceID, 12, 7, @on
                                            exec sp_trace_setevent @TraceID, 12, 15, @on
                                            exec sp_trace_setevent @TraceID, 12, 31, @on
                                            exec sp_trace_setevent @TraceID, 12, 8, @on
                                            exec sp_trace_setevent @TraceID, 12, 16, @on
                                            exec sp_trace_setevent @TraceID, 12, 48, @on
                                            exec sp_trace_setevent @TraceID, 12, 64, @on
                                            exec sp_trace_setevent @TraceID, 12, 1, @on
                                            exec sp_trace_setevent @TraceID, 12, 9, @on
                                            exec sp_trace_setevent @TraceID, 12, 17, @on
                                            exec sp_trace_setevent @TraceID, 12, 41, @on
                                            exec sp_trace_setevent @TraceID, 12, 49, @on
                                            exec sp_trace_setevent @TraceID, 12, 6, @on
                                            exec sp_trace_setevent @TraceID, 12, 10, @on
                                            exec sp_trace_setevent @TraceID, 12, 14, @on
                                            exec sp_trace_setevent @TraceID, 12, 18, @on
                                            exec sp_trace_setevent @TraceID, 12, 26, @on
                                            exec sp_trace_setevent @TraceID, 12, 50, @on
                                            exec sp_trace_setevent @TraceID, 12, 66, @on
                                            exec sp_trace_setevent @TraceID, 12, 3, @on
                                            exec sp_trace_setevent @TraceID, 12, 11, @on
                                            exec sp_trace_setevent @TraceID, 12, 35, @on
                                            exec sp_trace_setevent @TraceID, 12, 51, @on
                                            exec sp_trace_setevent @TraceID, 12, 4, @on
                                            exec sp_trace_setevent @TraceID, 12, 12, @on
                                            exec sp_trace_setevent @TraceID, 12, 60, @on
                                            exec sp_trace_setevent @TraceID, 12, 13, @on


                                            -- Set the Filters
                                            declare @intfilter int
                                            declare @bigintfilter bigint


                                            exec sp_trace_setfilter @TraceID, 35, 0, 6, N'";
                                                 sqlcreateproc=sqlcreateproc +  Database ;
                                                sqlcreateproc=sqlcreateproc+ @"'-- Set the trace status to start
                                                exec sp_trace_setstatus @TraceID, 1

                                                -- display trace id for future references
                                                select TraceID=@TraceID


                                                error: 
                                                select ErrorCode=@rc

                                                ";

                        SqlCommand cmdcreate = new SqlCommand(sqlcreateproc, cn);
                        cmdcreate.ExecuteNonQuery();
                                           

                       

                        //Job Exists delete the job

                       String sqlJobDelete = @"USE [msdb]
                            IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture_Trace')
                                EXEC msdb.dbo.sp_delete_job @job_name='"+ jobName.Trim() +"', @delete_unused_schedule=1";
                        SqlCommand cmdJobDelete = new SqlCommand(sqlJobDelete, cn);
                        cmdJobDelete.ExecuteNonQuery();



                        //Create the Job

                        String sqlJobCreate= @"USE [msdb]
                                        BEGIN TRANSACTION
                                    DECLARE @ReturnCode INT
                                    SELECT @ReturnCode = 0

                            IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
                            BEGIN
                            EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
                            IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

                            END

                            DECLARE @jobId BINARY(16)
                            EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name='"+ jobName.Trim() ;
		                     sqlJobCreate=sqlJobCreate + @"', @enabled=1, 
		                    @notify_level_eventlog=0, 
		                    @notify_level_email=0, 
		                    @notify_level_netsend=0, 
		                    @notify_level_page=0, 
		                    @delete_level=0, 
		                    @description=N' This jobs is created to capture Trace for database  "+ Database  +@"', 
		                    @category_name=N'[Uncategorized (Local)]', 
		                    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
                            IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


                          

                                EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StartTrace by Executing Stored Procedure', 
		                                @step_id=1, 
		                                @cmdexec_success_code=0, 
		                                @on_success_action=1, 
		                                @on_success_step_id=0, 
		                                @on_fail_action=2, 
		                                @on_fail_step_id=0, 
		                                @retry_attempts=0, 
		                                @retry_interval=0, 
		                                @os_run_priority=0, @subsystem=N'TSQL', 
		                                @command=N'Declare @Filename nvarchar(250)
                                Declare @StopTime datetime

                                select @Filename= SUBSTRING([filename],0, CHARINDEX([name],[filename],0))+''HealthCheck_Trace_" + jobName.Trim() + @"''  from sys.sysaltfiles where dbid=1 and fileid=1



                                set @StopTime=""" + strStopTime + "\"";



                                sqlJobCreate=sqlJobCreate+ @" 
                                DECLARE @rc int, @dbaTraceID int 
                                 EXEC @rc = master.dbo.Proc_CaptureTrace_"+jobName.Trim() +@"  @StopTime,@Filename,@dbaTraceID  OUTPUT 
                                ', 
		                                @database_name=N'master', 
		                                @output_file_name=N'C:\" + jobName.Trim() +@"_Capture_Trace.out', 
		                                @flags=0
                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
                                EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
                                EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule for Job', 
		                                @enabled=1, 
		                                @freq_type=1, 
		                                @freq_interval=0, 
		                                @freq_subday_type=0, 
		                                @freq_subday_interval=0, 
		                                @freq_relative_interval=0, 
		                                @freq_recurrence_factor=0, 
		                                @active_start_date=" + strStartdate;

                                       sqlJobCreate = sqlJobCreate + @",@active_end_date=99991231,@active_start_time=" + strStartTime;
		
		                                sqlJobCreate = sqlJobCreate + @",  @active_end_time=235959, 
		                                @schedule_uid=N'8339d412-49f7-4290-8687-0d3b0ac8107e'
                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
                                EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
                                COMMIT TRANSACTION
                                GOTO EndSave
                                QuitWithRollback:
                                    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
                                EndSave:

                        
                                ";


                                SqlCommand cmdJobCreate = new SqlCommand(sqlJobCreate, cn);
                                cmdJobCreate.ExecuteNonQuery();

                    }
                     
       
                }

                else
                {
                    //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillDatabaseTableDetails: ", " There were problems in importing PerformanceCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
                }

            }


            catch (Exception ex)
            {

             
                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " Some error occured in SQLAgentCheck.Please check exception file for details");


            }



        }


        private static string AddPrefixtoString(string STR1 )
        {
            if (STR1.Trim().Length == 1)
            {
                return "0" + STR1;
            }

            return STR1;
        }

        // Inserts the Server Job details in Trace_Server table and generates a Job Name
        private static string GetJobName(string SQLServerInstance, string Database, String Startdate, String EndDate,String StartTime,String EndTime)
        {
               //Open conection to the SQLServerHealthCheck Deploy database
                SqlConnection cnHealthCheck = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"]);
                cnHealthCheck.Open();
                
                //insert the details of Trace File into TraceFile table
                string strTraceFile = "insert into Trace_Server(ServerName,DatabaseName,TraceStartDate,TraceEndDate,TraceStartTime,TraceEndTime) values('" + SQLServerInstance + "','" + Database + "','" + Startdate + "','" + EndDate + "','" + StartTime + "','" + EndTime + "')";
                SqlCommand ocmdTrace = new SqlCommand(strTraceFile, cnHealthCheck);
                ocmdTrace.ExecuteNonQuery();

                //selects the last generated trace id
                strTraceFile = "select SCOPE_IDENTITY()";
                ocmdTrace.CommandText = strTraceFile;

                return "Capture_Trace_"+ ocmdTrace.ExecuteScalar().ToString();
        }

        //this method import the trace details from a remote server.The trace file is stored in the remote server
        public static void ImportTraceDetails(string SQLServerInstance, string DatabaseName)
        {

            try
            {
                //Chekcs if source Server of trace is accessible
                if (InstallationCheck.IsSQLServerAccessible(@"Server=" + SQLServerInstance + ";database=master;Integrated Security=True"))
                {

                    //Open conection to the SQLServerHealthCheck Deploy database
                       SqlConnection cnHealthCheck = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"]);
                       cnHealthCheck.Open();
                     

                        //check if source server is already added as linked server,if so drop it
                        string strquery = @"IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'IMPORT_TRACE_DETAILS')EXEC master.dbo.sp_dropserver @server=N'IMPORT_TRACE_DETAILS', @droplogins='droplogins'";
                        SqlCommand cmdlinkedserver = new SqlCommand(strquery, cnHealthCheck);
                        cmdlinkedserver.ExecuteNonQuery();

                        //adds the  source server as  linked server
                        strquery = @"EXEC master.dbo.sp_addlinkedserver @server = N'IMPORT_TRACE_DETAILS', @srvproduct=N'SQLServer', @provider=N'SQLNCLI', @datasrc=N'" + SQLServerInstance +"'";
                        cmdlinkedserver.CommandText = strquery;
                        cmdlinkedserver.ExecuteNonQuery();

                        //adds the linked server login
                        strquery = @"EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IMPORT_TRACE_DETAILS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL";
                        cmdlinkedserver.CommandText = strquery;
                        cmdlinkedserver.ExecuteNonQuery();

                          //Open connnection to the source server    
                        using (SqlConnection cnsource = new SqlConnection(@"Server=" + SQLServerInstance + ";Database=master;Integrated Security=True"))
                        {
                            cnsource.Open();

                            //if exists drop  the  table in source server to hold trace details

                            string strsourcequery = @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trace_HealthCheck]') AND type in (N'U'))
                            DROP TABLE [dbo].[Trace_HealthCheck]";
                            SqlCommand cmdsourceserver = new SqlCommand(strsourcequery, cnsource);
                            cmdsourceserver.ExecuteNonQuery();

                            //creates a table in source server master database to hold trace details
                            strsourcequery = @"CREATE TABLE [dbo].[Trace_HealthCheck](
	                                    	    [EventClass] [int] NULL,
	                                            [TextData] [ntext] NULL,
	                                            [ApplicationName] [nvarchar](128) NULL,
	                                            [NTUserName] [nvarchar](128) NULL,
	                                            [LoginName] [nvarchar](128) NULL,
	                                            [CPU] [int] NULL,
	                                            [Reads] [bigint] NULL,
	                                            [Writes] [bigint] NULL,
	                                            [Duration] [bigint] NULL,
	                                            [ClientProcessID] [int] NULL,
	                                            [SPID] [int] NULL,
	                                            [StartTime] [datetime] NULL,
	                                            [EndTime] [datetime] NULL,
	                                            [BinaryData] [image] NULL,
                                               ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";

                            cmdsourceserver.CommandText = strsourcequery;
                            cmdsourceserver.ExecuteNonQuery();

                            //import the data from file in table Trace_HealthCheck in source server table

                            strsourcequery = @"
                            Declare @Filename nvarchar(250)
                            Declare @SQL varchar(8000)
                            select @Filename= SUBSTRING([filename],0, CHARINDEX([name],[filename],0))+'HealthCheck_Trace.trc'  from sys.sysaltfiles where dbid=1 and fileid=1
                            set @SQL='INSERT INTO  Trace_HealthCheck SELECT EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                        ClientProcessID,SPID,StartTime,EndTime,BinaryData
                                      FROM fn_trace_gettable('''+ @Filename +''',default)'
                                     exec(@SQL) ";

                            cmdsourceserver.CommandText = strsourcequery;
                            cmdsourceserver.ExecuteNonQuery();
                           

                        }


                        //inserts into Trace_HealthCheck in SQLServerHealthCheckDeploy database in destination
                        strquery = @"INSERT INTO  Trace_HealthCheck(EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                        ClientProcessID,SPID,StartTime,EndTime,BinaryData) SELECT EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                        ClientProcessID,SPID,StartTime,EndTime,BinaryData FROM IMPORT_TRACE_DETAILS.master.dbo.Trace_HealthCheck";
                        cmdlinkedserver.CommandText = strquery;
                        cmdlinkedserver.Connection = cnHealthCheck;
                        cmdlinkedserver.ExecuteNonQuery();

                        MessageBox.Show("The details about trace Imported", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    
                }

                else
                {
                    MessageBox.Show("Couldn't connect to SQL Server Instance :" + SQLServerInstance + "  .Please check the connectivity", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }

            catch (Exception ex1)
            {

                MessageBox.Show(ex1.Message.ToString(), "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            
        }

        //this method imports the trace details from the selected trace file.The trace file is stored in local host
        public static void ImportTraceDetailsfromFile(string strFileName)
        {
            try
            {
                long lngTraceFile_id = 0;
                
                 //Open conection to the SQLServerHealthCheck Deploy database
                SqlConnection cnHealthCheck = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"]);
                cnHealthCheck.Open();
                                        

                //insert the details of Trace File into TraceFile table
                string strTraceFile="insert into Trace_File(StartTime,TraceFileName) values('" + DateTime.Now + "','" + strFileName +"')";
                SqlCommand ocmdTrace = new SqlCommand(strTraceFile, cnHealthCheck);
                ocmdTrace.ExecuteNonQuery();

                //selects the last generated trace id
                strTraceFile = "select SCOPE_IDENTITY()";
                ocmdTrace.CommandText = strTraceFile;
                lngTraceFile_id = Convert.ToInt64(ocmdTrace.ExecuteScalar().ToString());

                //Reads the trace file and inserts the details into TraceDetails_File table
                TraceFile trcFile = new TraceFile();
                trcFile.InitializeAsReader(strFileName);
                while (trcFile.Read())
                {
                   
                        strTraceFile = CreateInsertQueryfromTraceFile(trcFile, lngTraceFile_id);//Creates the insert query
                        ocmdTrace.CommandText = strTraceFile;
                        MessageBox.Show(strTraceFile);
                        ocmdTrace.ExecuteNonQuery();

                }

                //updates the end time of selected Trace file in  TraceFile table
                strTraceFile = "update Trace_File set EndTime='" + DateTime.Now + "' where TraceFileId=" + lngTraceFile_id;
                ocmdTrace.CommandText = strTraceFile;
                ocmdTrace.ExecuteNonQuery();
                trcFile.Close();
                MessageBox.Show("The details about trace Imported", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
               
            }

            catch (Exception ex1)
            {

                MessageBox.Show(ex1.Message.ToString() , "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
              
            }


        }

        //this method  generates the insert query for importing the  details from Trace File
        private static string CreateInsertQueryfromTraceFile(TraceFile trcFile,long lngTraceFile_id)
        {

            String strquerycols = " insert into TraceDetails_File(TraceFileId,EventClass";
            String strvalues = "values(" + lngTraceFile_id + ",'" + trcFile.GetString(trcFile.GetOrdinal("EventClass")) + "'"; 

            //If TextData col is not null
           
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("TextData"))))
            {
                strquerycols = strquerycols + ",TextData";

                string strTextData = trcFile.GetString(trcFile.GetOrdinal("TextData")).ToString();
                strTextData = strTextData.Replace("'", "\"");
                strvalues = strvalues + ",'" + strTextData + "'";
            }

            //If ApplicationName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("ApplicationName"))))
            {
                strquerycols = strquerycols + ",ApplicationName";
                strvalues = strvalues + ",'" + trcFile.GetString(trcFile.GetOrdinal("ApplicationName")).ToString() + "'";
            }

            //If NTUserName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("NTUserName"))))
            {
                strquerycols = strquerycols + ",NTUserName";
                strvalues = strvalues + ",'" + trcFile.GetString(trcFile.GetOrdinal("NTUserName")).ToString() + "'";
            }

            //If LoginName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("LoginName"))))
            {
                strquerycols = strquerycols + ",LoginName";
                strvalues = strvalues + ",'" + trcFile.GetString(trcFile.GetOrdinal("LoginName")).ToString() + "'";
            }

           //If CPU col is not null
             if (!(trcFile.IsDBNull(trcFile.GetOrdinal("CPU"))))
            {
                strquerycols = strquerycols + ",CPU";
                strvalues = strvalues + "," +trcFile.GetInt32(trcFile.GetOrdinal("CPU")).ToString();
            }

            //If Reads col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("Reads"))))
            {
                strquerycols = strquerycols + ",Reads";
                strvalues = strvalues + "," +trcFile.GetInt64(trcFile.GetOrdinal("Reads")).ToString();
            }

            //If Writes col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("Writes"))))
            {
                strquerycols = strquerycols + ",Writes";
                strvalues = strvalues + "," + trcFile.GetInt64(trcFile.GetOrdinal("Writes")).ToString();
            }

            //If Duration col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("Duration"))))
            {
                strquerycols = strquerycols + ",Duration";
                strvalues = strvalues + "," + trcFile.GetInt64(trcFile.GetOrdinal("Duration")).ToString();
            }

            //If ClientProcessID col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("ClientProcessID")))) 
            {
                strquerycols = strquerycols + ",ClientProcessID";
                strvalues = strvalues + "," +trcFile.GetInt32( trcFile.GetOrdinal("ClientProcessID")).ToString();
            }


            //If SPID col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("SPID")))) 
            {
                strquerycols = strquerycols + ",SPID";
                strvalues = strvalues + "," + trcFile.GetInt32(trcFile.GetOrdinal("SPID")).ToString();
            }

            //If StartTime col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("StartTime")))) 
            {
                strquerycols = strquerycols + ",StartTime";
                strvalues = strvalues + ",'" + trcFile.GetDateTime(trcFile.GetOrdinal("StartTime")) + "'"; 
            }

            //If EndTime col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("EndTime")))) 
            {
                strquerycols = strquerycols + ",EndTime";
                strvalues = strvalues + ",'" + trcFile.GetDateTime(trcFile.GetOrdinal("EndTime")) + "'";
            }

            //If ServerName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("ServerName")))) 
            {
                strquerycols = strquerycols + ",ServerName";
                strvalues = strvalues + ",'" + trcFile.IsDBNull(trcFile.GetOrdinal("ServerName")) + "'";
            }


            //If DatabaseName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("DatabaseName")))) 
            {
                strquerycols = strquerycols + ",DatabaseName";
                strvalues = strvalues + ",'" + trcFile.IsDBNull(trcFile.GetOrdinal("DatabaseName")) + "'";
            }

            //If ObjectName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("ObjectName"))))
            {
                strquerycols = strquerycols + ",ObjectName";
                strvalues = strvalues + ",'" + trcFile.IsDBNull(trcFile.GetOrdinal("ObjectName")) + "'";
            }
        
          
            //If HostName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("HostName"))))
            {
                strquerycols = strquerycols + ",HostName";
                strvalues = strvalues + ",'" + trcFile.IsDBNull(trcFile.GetOrdinal("HostName")) + "'";
            }



            return strquerycols + ")" + strvalues + ")";
        }

        public static void ImportTraceDetailsfromServer(string[] strselectedindex)
        {

            try
            {
              

                    //Open conection to the SQLServerHealthCheck Deploy database
                    SqlConnection cnHealthCheck = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"]);
                    cnHealthCheck.Open();

                    string SQLServerInstance = "";
                    
                     // loops through the selected ServerTraceId and imports the data
                    foreach(string s in strselectedindex)
                    {
                        //with ServerTraceId get the SQLServerInstance name on which the trace job was created
                        string strindex = s;
                        string strServerdetailsquery = "select ServerName from Trace_Server where ServerTraceId=" + Convert.ToInt64(strindex);
                        SqlCommand cmdserverdetails = new SqlCommand(strServerdetailsquery, cnHealthCheck);
                        SQLServerInstance = cmdserverdetails.ExecuteScalar().ToString();

                        //Checks if source Server of trace is accessible
                        if (InstallationCheck.IsSQLServerAccessible(@"Server=" + SQLServerInstance + ";database=master;Integrated Security=True"))
                        {

                            //check if source server is already added as linked server,if so drop it
                            string strquery = @"IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'IMPORT_TRACE_DETAILS')EXEC master.dbo.sp_dropserver @server=N'IMPORT_TRACE_DETAILS', @droplogins='droplogins'";
                            SqlCommand cmdlinkedserver = new SqlCommand(strquery, cnHealthCheck);
                            cmdlinkedserver.ExecuteNonQuery();

                            //adds the  source server as  linked server
                            strquery = @"EXEC master.dbo.sp_addlinkedserver @server = N'IMPORT_TRACE_DETAILS', @srvproduct=N'SQLServer', @provider=N'SQLNCLI', @datasrc=N'" + SQLServerInstance + "'";
                            cmdlinkedserver.CommandText = strquery;
                            cmdlinkedserver.ExecuteNonQuery();

                            //adds the linked server login
                            strquery = @"EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IMPORT_TRACE_DETAILS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL";
                            cmdlinkedserver.CommandText = strquery;
                            cmdlinkedserver.ExecuteNonQuery();

                            //Open connnection to the source server    
                            using (SqlConnection cnsource = new SqlConnection(@"Server=" + SQLServerInstance + ";Database=master;Integrated Security=True"))
                            {
                                cnsource.Open();

                                //if exists drop  the  table in source server to hold trace details

                                string strsourcequery = @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trace_HealthCheck]') AND type in (N'U'))
                                DROP TABLE [dbo].[Trace_HealthCheck]";
                                SqlCommand cmdsourceserver = new SqlCommand(strsourcequery, cnsource);
                                cmdsourceserver.ExecuteNonQuery();

                                //creates a table in source server master database to hold trace details
                                strsourcequery = @"CREATE TABLE [dbo].[Trace_HealthCheck](
	                                    	       [EventClass] [nvarchar](250) NULL,
	                                                [TextData] [ntext] NULL,
	                                                [ApplicationName] [nvarchar](128) NULL,
	                                                [NTUserName] [nvarchar](128) NULL,
	                                                [LoginName] [nvarchar](128) NULL,
	                                                [CPU] [int] NULL,
	                                                [Reads] [bigint] NULL,
	                                                [Writes] [bigint] NULL,
	                                                [Duration] [bigint] NULL,
	                                                [ClientProcessID] [int] NULL,
	                                                [SPID] [int] NULL,
	                                                [StartTime] [datetime] NULL, 
	                                                [EndTime] [datetime] NULL,
	                                                [ServerName] [nvarchar](256) NULL,
	                                                [DatabaseName] [nvarchar](256) NULL,
	                                                [ObjectName] [nvarchar](256) NULL,
	                                                [HostName] [nvarchar](256) NULL
                                                   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";

                                cmdsourceserver.CommandText = strsourcequery;
                                cmdsourceserver.ExecuteNonQuery();

                                //import the data from file in table Trace_HealthCheck in source server table
                              
                                strsourcequery = @"
                                Declare @Filename nvarchar(250)
                                Declare @SQL varchar(8000)
                                select @Filename= SUBSTRING([filename],0, CHARINDEX([name],[filename],0))+'HealthCheck_Trace_Capture_Trace_"+ strindex+ @".trc'  from sys.sysaltfiles where dbid=1 and fileid=1
                                set @SQL='INSERT INTO  Trace_HealthCheck SELECT EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                            ClientProcessID,SPID,StartTime,EndTime,ServerName,DatabaseName,ObjectName,HostName
                                            FROM fn_trace_gettable('''+ @Filename +''',default)'
                                         exec(@SQL) ";

                                cmdsourceserver.CommandText = strsourcequery;
                                cmdsourceserver.ExecuteNonQuery();


                            }

                            //delete the details if exists previously in  for same ServerTraceId
                            strquery = @"delete from  TraceDetails_Server where ServerTraceId="  +Convert.ToInt64(strindex);
                            cmdlinkedserver.CommandText = strquery;
                            cmdlinkedserver.Connection = cnHealthCheck;
                            cmdlinkedserver.ExecuteNonQuery();

                            //inserts into TraceDetails_Server in SQLServerHealthCheckDeploy database in destination
                            strquery = @"INSERT INTO  TraceDetails_Server(ServerTraceId,EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                            ClientProcessID,SPID,StartTime,EndTime,ServerName,DatabaseName,ObjectName,HostName) SELECT " + strindex + @",EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                            ClientProcessID,SPID,StartTime,EndTime,ServerName,DatabaseName,ObjectName,HostName FROM IMPORT_TRACE_DETAILS.master.dbo.Trace_HealthCheck";
                            cmdlinkedserver.CommandText = strquery;
                            cmdlinkedserver.Connection = cnHealthCheck;
                            cmdlinkedserver.ExecuteNonQuery();

                            MessageBox.Show("The details were imported successfully from HealthCheck_Trace_Capture_Trace_"+ strindex+ @".trc."  , "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);

                        }

                        else
                        {
                            MessageBox.Show("Couldn't connect to SQL Server Instance :" + SQLServerInstance + "  .Please check the connectivity", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                    }
            }

            catch (Exception ex1)
            {

                MessageBox.Show(ex1.Message.ToString(), "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }


        }


        private static string FixComments(string sql)
        {
            string str = sql;
            int num = 0;
            int startat = 0;
            Match match = _delimiterStart.Match(sql, startat);
            while (match.Success)
            {
                string str4 = match.Value;
                if (str4 == null)
                {
                    return sql;
                }
                if (!(str4 == "'"))
                {
                    if (str4 != "--")
                    {
                        if (str4 != "/*")
                        {
                            return sql;
                        }
                        goto Label_00C7;
                    }
                }
                else
                {
                    Match match2 = _stringConstant.Match(sql, match.Index);
                    if (match2.Success)
                    {
                        startat = match.Index + match2.Length;
                    }
                    else
                    {
                        startat++;
                    }
                    goto Label_00E3;
                }
                startat = match.Index;
                if (_inlineComment.Match(sql, startat).Success)
                {
                    sql = _inlineComment.Replace(sql, "", 1, startat);
                }
                else
                {
                    startat++;
                }
                goto Label_00E3;
            Label_00C7:
                startat = match.Index;
                sql = RemoveBlockComments(sql, startat);
                startat++;
            Label_00E3:
                if (startat >= sql.Length)
                {
                    return sql;
                }
                match = _delimiterStart.Match(sql, startat);
                num++;
                if (num > 0x2710)
                {
                    string str2 = Assembly.GetExecutingAssembly().GetName().Version.ToString();
                    throw new SqlParsingException(("Infinite loop in FixComments (" + str2 + ")" + Environment.NewLine + Environment.NewLine) + str);
                }
            }
            return sql;
        }



        public static string NormalizeSqlText(string sql, string eventClass, int spid)
        {
            return NormalizeSqlText(sql, eventClass, spid, true);
        }


        public static string NormalizeSqlText(string sql, string eventClass, int spid, bool spreadCsv)
        {
            Match match;
            if (sql == null)
            {
                return "";
            }
            bool flag = false;
            bool flag2 = false;
            int num = 0;
            string key = "";
            if (sql == "sp_reset_connection")
            {
                return "";
            }
            sql = FixComments(sql);
            sql = _spaces.Replace(sql, " ").ToUpper(CultureInfo.InvariantCulture);
            if ((eventClass == "RPC:Completed") || (eventClass == "RPC:Started"))
            {
                sql = _doubleApostrophe.Replace(sql, "{STR}");
                match = _prepExecRpc.Match(sql);
                if (match.Success)
                {
                    sql = match.Groups["statement"].ToString();
                }
                match = _prepareSql.Match(sql);
                if (match.Success)
                {
                    if (match.Groups["preptype"].ToString().ToLower() == "sp_prepare")
                    {
                        flag2 = true;
                    }
                    if (match.Groups["stmtnum"].ToString() == "NULL")
                    {
                        num = 0;
                    }
                    else
                    {
                        num = Convert.ToInt32(match.Groups["stmtnum"].ToString());
                    }
                    sql = match.Groups["remaining"].ToString();
                    match = _preppedSqlStatement.Match(sql);
                    if (match.Success)
                    {
                        sql = match.Groups["statement"].ToString();
                        sql = _doubleApostrophe.Replace(sql, "'${string}'");
                    }
                    flag = true;
                }
                match = null;
                match = _execPrepped.Match(sql);
                if (match.Success)
                {
                    num = Convert.ToInt32(match.Groups["stmtnum"].ToString());
                    if (prepSql.ContainsKey(spid.ToString() + "_" + num.ToString()))
                    {
                        return TruncateSql("{PREPARED} " + prepSql[spid.ToString() + "_" + num.ToString()].ToString());
                    }
                }
                match = null;
                match = _execUnprep.Match(sql);
                if (match.Success)
                {
                    num = Convert.ToInt32(match.Groups["stmtnum"].ToString());
                    key = spid.ToString() + "_" + num.ToString();
                    if (prepSql.ContainsKey(key))
                    {
                        sql = prepSql[key].ToString();
                        prepSql.Remove(spid.ToString() + "_" + num.ToString());
                        match = null;
                        return TruncateSql("{UNPREPARING} " + sql);
                    }
                }
                match = null;
            }
            if ((eventClass == "RPC:Completed") || (eventClass == "RPC:Started"))
            {
                match = _cursor.Match(sql);
                if (match.Success)
                {
                    sql = "{CURSOR} " + match.Groups["statement"].ToString();
                    sql = _doubleApostrophe.Replace(sql, "'${string}'");
                }
                match = _cursorPrepExec.Match(sql);
                if (match.Success)
                {
                    sql = "{CURSOR} " + match.Groups["statement"].ToString();
                    sql = _doubleApostrophe.Replace(sql, "'${string}'");
                }
                match = _spExecuteSql.Match(sql);
                if (match.Success)
                {
                    sql = match.Groups["statement"].ToString();
                }
                match = null;
                if (!_brackets.Match(sql).Success)
                {
                    match = _dbAndObjectName.Match(sql);
                    if (match.Success)
                    {
                        sql = match.Groups["object"].ToString();
                    }
                    else
                    {
                        match = _objectName.Match(sql);
                        if (match.Success)
                        {
                            sql = match.Groups["object"].ToString();
                        }
                    }
                    if (((sql == "SP_CURSOR") || (sql == "SP_CURSORFETCH")) || ((sql == "SP_CURSORCLOSE") || (sql == "SP_RESET_CONNECTION")))
                    {
                        match = null;
                        return "";
                    }
                }
                match = null;
            }
            sql = _emptyString.Replace(sql, "{STR}");
            sql = _stringConstant.Replace(sql, "{STR}");
            sql = _unicodeConstant.Replace(sql, "{NSTR}");
            sql = _binaryConstant.Replace(sql, "{BINARY}");
            sql = _numericConstant.Replace(sql, "${prefix}{##}");
            sql = _inClause.Replace(sql, "{IN}");
            if (spreadCsv)
            {
                sql = _spreadCsv.Replace(sql, ", ");
            }
            sql = _spaces.Replace(sql, " ");
            sql = TruncateSql(sql);
            if (flag && (num != 0))
            {
                if (!prepSql.ContainsKey(spid.ToString() + "_" + num.ToString()))
                {
                    prepSql.Add(spid.ToString() + "_" + num.ToString(), sql);
                }
                else
                {
                    prepSql[spid.ToString() + "_" + num.ToString()] = sql;
                }
            }
            match = null;
            if (flag2)
            {
                return TruncateSql("{PREPARING} " + sql);
            }
            if (flag && !flag2)
            {
                return TruncateSql("{PREPARED} " + sql);
            }
            return TruncateSql(sql);
        }

        private static string RemoveBlockComments(string sql, int position)
        {
            StringBuilder builder = new StringBuilder(sql.Length);
            builder.Append(sql.Substring(0, position));
            int num = 0;
            int startIndex = position;
            while (startIndex < (sql.Length - 1))
            {
                string str = sql.Substring(startIndex, 2);
                if (str == null)
                {
                    goto Label_0065;
                }
                if (!(str == "/*"))
                {
                    if (str == "*/")
                    {
                        goto Label_0057;
                    }
                    goto Label_0065;
                }
                num++;
                startIndex++;
                startIndex++;
                goto Label_0069;
            Label_0057:
                num--;
                startIndex++;
                startIndex++;
                goto Label_0069;
            Label_0065:
                startIndex++;
            Label_0069:
                if (num == 0)
                {
                    if (startIndex < sql.Length)
                    {
                        builder.Append(sql.Substring(startIndex, sql.Length - startIndex));
                    }
                    return builder.ToString();
                }
            }
            return sql;
        }

        private static string TruncateSql(string sql)
        {
            sql = sql.Trim();
            if (sql.Length > 0xfa0)
            {
                return sql.Substring(0, 0xfa0);
            }
            return sql;
        }


    }

}
